﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Configuration;
using System.Data.SqlClient;
using ZWL.DBUtility;//请先添加引用

namespace ZWL.BLL
{
   public class StuffLibrary
    {
        public StuffLibrary() { }

        #region Model
        private int _id;
        private string _ZhaoBiaoNumber;
        private string _StuffNameModel;
        private string _StuffCode;
        private string _StuffCode18;
        private string _JiLiangUnit;
        private string _JiShuCanShu;
        private string _GongYingShangName;
        private string _GongYingShangPhone;
        private string _GongYingShangSerils;

        /// <summary>
        /// 
        /// </summary>
        public int ID
        {
            set { _id = value; }
            get { return _id; }
        }

        /// <summary>
        /// 招标编号
        /// </summary>
        public string ZhaoBiaoNumber
        {
            set { _ZhaoBiaoNumber = value; }
            get { return _ZhaoBiaoNumber; }
        }
        /// <summary>
        /// 物资名称及规格型号
        /// </summary>
        public string StuffNameModel
        {
            set { _StuffNameModel = value; }
            get { return _StuffNameModel; }
        }
        /// <summary>
        /// 物资编码
        /// </summary>
        public string StuffCode
        {
            set { _StuffCode = value; }
            get { return _StuffCode; }
        }
        /// <summary>
        /// 18位物资编码
        /// </summary>
        public string StuffCode18
        {
            set { _StuffCode18 = value; }
            get { return _StuffCode18; }
        }
        /// <summary>
        /// 计量单位
        /// </summary>
        public string JiLiangUnit
        {
            set { _JiLiangUnit = value; }
            get { return _JiLiangUnit; }
        }
        /// <summary>
        /// 技术参数
        /// </summary>
        public string JiShuCanShu
        {
            set { _JiShuCanShu = value; }
            get { return _JiShuCanShu; }
        }
        /// <summary>
        /// 供应商名称
        /// </summary>
        public string GongYingShangName
        {
            set { _GongYingShangName = value; }
            get { return _GongYingShangName; }
        }
        /// <summary>
        /// 供应商联系方式
        /// </summary>
        public string GongYingShangPhone
        {
            set { _GongYingShangPhone = value; }
            get { return _GongYingShangPhone; }
        }
        /// <summary>
        /// 供应商编码
        /// </summary>
        public string GongYingShangSerils
        {
            set { _GongYingShangSerils = value; }
            get { return _GongYingShangSerils; }
        }

        #endregion Model

        #region  成员方法

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public StuffLibrary(int ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,ZhaoBiaoNumber,StuffNameModel,StuffCode,StuffCode18,JiLiangUnit,JiShuCanShu,GongYingShangName,GongYingShangPhone,GongYingShangSerils ");
            strSql.Append(" FROM StuffLibrary ");
            strSql.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)};
            parameters[0].Value = ID;

            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["ID"].ToString() != "")
                {
                    ID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                }
                ZhaoBiaoNumber = ds.Tables[0].Rows[0]["ZhaoBiaoNumber"].ToString() != "" ? ds.Tables[0].Rows[0]["ZhaoBiaoNumber"].ToString() : "";
                StuffNameModel = ds.Tables[0].Rows[0]["StuffNameModel"].ToString() != "" ? ds.Tables[0].Rows[0]["StuffNameModel"].ToString() : "";
                StuffCode = ds.Tables[0].Rows[0]["StuffCode"].ToString() != "" ? ds.Tables[0].Rows[0]["StuffCode"].ToString() : "";
                StuffCode18 = ds.Tables[0].Rows[0]["StuffCode18"].ToString() != "" ? ds.Tables[0].Rows[0]["StuffCode18"].ToString() : "";
                JiLiangUnit = ds.Tables[0].Rows[0]["JiLiangUnit"].ToString() != "" ? ds.Tables[0].Rows[0]["JiLiangUnit"].ToString() : "";
                JiShuCanShu = ds.Tables[0].Rows[0]["JiShuCanShu"].ToString() != "" ? ds.Tables[0].Rows[0]["JiShuCanShu"].ToString() : "";
                GongYingShangName = ds.Tables[0].Rows[0]["GongYingShangName"].ToString() != "" ? ds.Tables[0].Rows[0]["GongYingShangName"].ToString() : "";
                GongYingShangPhone = ds.Tables[0].Rows[0]["GongYingShangPhone"].ToString() != "" ? ds.Tables[0].Rows[0]["GongYingShangPhone"].ToString() : "";
                GongYingShangSerils = ds.Tables[0].Rows[0]["GongYingShangSerils"].ToString() != "" ? ds.Tables[0].Rows[0]["GongYingShangSerils"].ToString() : "";

            }
        }

        /// <summary>
        /// 得到最大ID
        /// </summary>
        public int GetMaxId()
        {
            return DbHelperSQL.GetMaxID("ID", "StuffLibrary");
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from StuffLibrary");
            strSql.Append(" where ID=@ID ");

            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)};
            parameters[0].Value = ID;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into StuffLibrary(");
            strSql.Append("ZhaoBiaoNumber,StuffNameModel,StuffCode,StuffCode18,JiLiangUnit,JiShuCanShu,GongYingShangName,GongYingShangPhone,GongYingShangSerils)");
            strSql.Append(" values (");
            strSql.Append("@ZhaoBiaoNumber,@StuffNameModel,@StuffCode,@StuffCode18,@JiLiangUnit,@JiShuCanShu,@GongYingShangName,@GongYingShangPhone,@GongYingShangSerils)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@ZhaoBiaoNumber", SqlDbType.VarChar,50),
					new SqlParameter("@StuffNameModel", SqlDbType.VarChar,100),
					new SqlParameter("@StuffCode", SqlDbType.VarChar,50),
					new SqlParameter("@StuffCode18", SqlDbType.VarChar,50),
					new SqlParameter("@JiLiangUnit", SqlDbType.VarChar,10),
					new SqlParameter("@JiShuCanShu", SqlDbType.VarChar,50),
					new SqlParameter("@GongYingShangName", SqlDbType.VarChar,50),
					new SqlParameter("@GongYingShangPhone", SqlDbType.VarChar,50),
					new SqlParameter("@GongYingShangSerils", SqlDbType.VarChar,50)};
            parameters[0].Value = ZhaoBiaoNumber;
            parameters[1].Value = StuffNameModel;
            parameters[2].Value = StuffCode;
            parameters[3].Value = StuffCode18;
            parameters[4].Value = JiLiangUnit;
            parameters[5].Value = JiShuCanShu;
            parameters[6].Value = GongYingShangName;
            parameters[7].Value = GongYingShangPhone;
            parameters[8].Value = GongYingShangSerils;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 1;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public void Update()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update StuffLibrary set ");
            strSql.Append("ZhaoBiaoNumber=@ZhaoBiaoNumber,");
            strSql.Append("StuffNameModel=@StuffNameModel,");
            strSql.Append("StuffCode=@StuffCode,");
            strSql.Append("StuffCode18=@StuffCode18,");
            strSql.Append("JiLiangUnit=@JiLiangUnit,");
            strSql.Append("JiShuCanShu=@JiShuCanShu,");
            strSql.Append("GongYingShangName=@GongYingShangName,");
            strSql.Append("GongYingShangPhone=@GongYingShangPhone,");
            strSql.Append("GongYingShangSerils=@GongYingShangSerils");
            strSql.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4),
					new SqlParameter("@ZhaoBiaoNumber", SqlDbType.VarChar,50),
					new SqlParameter("@StuffNameModel", SqlDbType.VarChar,100),
					new SqlParameter("@StuffCode", SqlDbType.VarChar,50),
					new SqlParameter("@StuffCode18", SqlDbType.VarChar,50),
					new SqlParameter("@JiLiangUnit", SqlDbType.VarChar,10),
					new SqlParameter("@JiShuCanShu", SqlDbType.VarChar,50),
					new SqlParameter("@GongYingShangName", SqlDbType.VarChar,50),
					new SqlParameter("@GongYingShangPhone", SqlDbType.VarChar,50),
					new SqlParameter("@GongYingShangSerils", SqlDbType.VarChar,50)};
            parameters[0].Value = ID;
            parameters[1].Value = ZhaoBiaoNumber;
            parameters[2].Value = StuffNameModel;
            parameters[3].Value = StuffCode;
            parameters[4].Value = StuffCode18;
            parameters[5].Value = JiLiangUnit;
            parameters[6].Value = JiShuCanShu;
            parameters[7].Value = GongYingShangName;
            parameters[8].Value = GongYingShangPhone;
            parameters[9].Value = GongYingShangSerils;

            DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 删除一条数据
        /// </summary>
        public void Delete(int ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from StuffLibrary ");
            strSql.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)};
            parameters[0].Value = ID;

            DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public void GetModel(int ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 ID,ZhaoBiaoNumber,StuffNameModel,StuffCode,StuffCode18,JiLiangUnit,JiShuCanShu,GongYingShangName,GongYingShangPhone,GongYingShangSerils ");
            strSql.Append(" FROM StuffLibrary ");
            strSql.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)};
            parameters[0].Value = ID;

            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["ID"].ToString() != "")
                {
                    ID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                }
                ZhaoBiaoNumber = ds.Tables[0].Rows[0]["ZhaoBiaoNumber"].ToString() != "" ? ds.Tables[0].Rows[0]["ZhaoBiaoNumber"].ToString() : "";
                StuffNameModel = ds.Tables[0].Rows[0]["StuffNameModel"].ToString() != "" ? ds.Tables[0].Rows[0]["StuffNameModel"].ToString() : "";
                StuffCode = ds.Tables[0].Rows[0]["StuffCode"].ToString() != "" ? ds.Tables[0].Rows[0]["StuffCode"].ToString() : "";
                StuffCode18 = ds.Tables[0].Rows[0]["StuffCode18"].ToString() != "" ? ds.Tables[0].Rows[0]["StuffCode18"].ToString() : "";
                JiLiangUnit = ds.Tables[0].Rows[0]["JiLiangUnit"].ToString() != "" ? ds.Tables[0].Rows[0]["JiLiangUnit"].ToString() : "";
                JiShuCanShu = ds.Tables[0].Rows[0]["JiShuCanShu"].ToString() != "" ? ds.Tables[0].Rows[0]["JiShuCanShu"].ToString() : "";
                GongYingShangName = ds.Tables[0].Rows[0]["GongYingShangName"].ToString() != "" ? ds.Tables[0].Rows[0]["GongYingShangName"].ToString() : "";
                GongYingShangPhone = ds.Tables[0].Rows[0]["GongYingShangPhone"].ToString() != "" ? ds.Tables[0].Rows[0]["GongYingShangPhone"].ToString() : "";
                GongYingShangSerils = ds.Tables[0].Rows[0]["GongYingShangSerils"].ToString() != "" ? ds.Tables[0].Rows[0]["GongYingShangSerils"].ToString() : "";
            }
        }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM StuffLibrary ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// Excel数据导入Datable
        /// </summary>
        /// <param name="fileUrl"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        public System.Data.DataTable GetExcelDatatable(string fileUrl, string table)
        {
            //office2007之前 仅支持.xls
            //const string cmdText = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;IMEX=1';";
            //支持.xls和.xlsx，即包括office2010等版本的   HDR=Yes代表第一行是标题，不是数据；
            const string cmdText = "Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
            System.Data.DataTable dt = null;
            //建立连接
            OleDbConnection conn = new OleDbConnection(string.Format(cmdText, fileUrl));
            try
            {
                //打开连接
                if (conn.State == ConnectionState.Broken || conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                //获取Excel的第一个Sheet名称
                string sheetName = schemaTable.Rows[0]["TABLE_NAME"].ToString().Trim();
                //查询sheet中的数据
                string strSql = "select * from [" + sheetName + "]";
                OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);
                DataSet ds = new DataSet();
                da.Fill(ds, table);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception exc)
            {
                throw exc;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }
        /// <summary>
        /// SqlBulkCopy导入数据到数据库
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public void SqlBulkCopyInsetData(System.Data.DataTable dt)
        {


            //数据库连接
            string strConnection = ConfigurationManager.ConnectionStrings["BDOAConnectionString"].ToString();
            SqlConnection sqlConnection = new SqlConnection(strConnection);
            sqlConnection.Open();

            //声明SqlBulkCopy ,using释放非托管资源
            using (SqlBulkCopy sqlBC = new SqlBulkCopy(strConnection))
            {
                //一次批量的插入的数据量
                sqlBC.BatchSize = 1000;
                //超时之前操作完成所允许的秒数，如果超时则事务不会提交 ，数据将回滚，所有已复制的行都会从目标表中移除
                sqlBC.BulkCopyTimeout = 60;

                //设置要批量写入的表
                sqlBC.DestinationTableName = "StuffLibrary";

                //自定义的datatable和数据库的字段进行对应
                sqlBC.ColumnMappings.Add("招标编号", "ZhaoBiaoNumber");
                sqlBC.ColumnMappings.Add("物资名称及规格型号", "StuffNameModel");
                sqlBC.ColumnMappings.Add("物资编码", "StuffCode");

                //批量写入
                sqlBC.WriteToServer(dt);
            }
            sqlConnection.Dispose();

        }



        #endregion  成员方法


    }
}